package org.kunteng.yunac.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeUtility;

import org.apache.commons.mail.EmailAttachment;
import org.apache.commons.mail.HtmlEmail;

/**
 * 运营需要每天通过邮件方式提供运营数据
 * 
 * 写一个定时程序，每天凌晨执行，向运营组邮箱（"运营"<online@kunteng.org>）发送邮件。
 * 
 */
public class OperationEmail {
  static String hostName = "smtp.exmail.qq.com"; // 发件服务器
  static String userName = "rdsp@kunteng.org"; // 发件服务器帐号
  static String password = "KTCLrd123"; // 发件服务器密码

  static String emailFrom = "rdsp@kunteng.org"; // 发件人地址
  static String nameFrom = ""; // 发件人名称

  static String emailTo = "oc-data@kunteng.org"; // 收件人地址
  static String nameTo = ""; // 收件人名称

  
  static String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
  static String fileName = "运营数据统计@" + date + ".csv";
//  static String path = "/opt/shell/";
  static String path = "D:/";

//  static String es_url = "http://192.168.18.31:9200/wifiauth/portal/_search";
   static String es_url = "http://192.168.20.26:9200/wifiauth/portal/_search";

  public static void main(String[] args) {
    List<Map<String, String>> uv_list = queryChannelUVFromES();
    if (uv_list != null && uv_list.size() > 0) {
      int flag = saveChannelUV(uv_list);
      if (flag == 1) {
        // 导出一级渠道运营数据
        int flag1 = exportOperationData("1");
        if (flag1 == 1) {
          // 导出二级渠道运营数据
          int flag2 = exportOperationData("2");
          if (flag2 == 1) {
            sendEmail();
          }
        }
      }
    }
  }

  /**
   * 发送运营数据邮件
   */
  public static int sendEmail() {
    int flag = 0;
    try {
      // 设置邮件信息
      HtmlEmail hemail = new HtmlEmail();
      hemail.setHostName(hostName);
      hemail.setAuthentication(userName, password);// 发件服务器的帐号和密码

      // 发送给单个收件人邮箱
      // hemail.addTo(emailTo,nameTo);

      /* 发送给多个收件人邮箱 */
      List<InternetAddress> inter = new ArrayList<InternetAddress>();
//      inter.add(new InternetAddress(emailTo));
      inter.add(new InternetAddress("zhangting@kunteng.org"));
      inter.add(new InternetAddress("luzhirui@kunteng.org"));
      hemail.setTo(inter);

      hemail.setFrom(emailFrom, nameFrom);// 发件人、发件人称呼

      hemail.setSubject("运营数据统计@" + date);// 主题
      hemail.setCharset("UTF-8");// 设置内容编码格式
      hemail.setHtmlMsg("请查看附件！");// 设置内容

      List<String> attach_list = new ArrayList<String>();
      attach_list.add("一级渠道" + fileName);
      attach_list.add("二级渠道" + fileName);

      // 添加附件
      try {
        for (String attach_f : attach_list) {
          EmailAttachment attachment = new EmailAttachment();
          attachment.setDisposition(EmailAttachment.ATTACHMENT);
          attachment.setName(MimeUtility.encodeText(attach_f)); // 设置附件名字
          attachment.setPath(path + attach_f);// 附件路径
          hemail.attach(attachment);// 添加附件
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
      hemail.send();

      // 邮件发送后删除磁盘上附件
      for (String attach_file : attach_list) {
        File f = new File(path + attach_file);
        // f.delete();
      }

      flag = 1;
      System.out.println("execute success!");
    } catch (Exception e) {
      e.printStackTrace();
    }
    return flag;
  }

  /**
   * 导出运营数据为csv文件
   * 
   * @param level
   * @return
   */
  public static int exportOperationData(String level) {
    int flag = 0;
    Connection conn = new DBConn().getConn();
    Connection conn1 = new DBConn().getConn();
    Connection conn2 = new DBConn().getConn();
    Connection conn3 = new DBConn().getConn();

    try {
      String file_name = fileName;
      if ("1".equals(level)) {
        file_name = "一级渠道" + fileName;
      } else if ("2".equals(level)) {
        file_name = "二级渠道" + fileName;
      }

      // FileWriter fw = new FileWriter(path + file_name);
      // BufferedWriter bw = new BufferedWriter(fw);
      File file = new File(path + file_name);
      BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file,false), "UTF-8"));
      byte b[] = { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF };
      bw.write(new String(b));
      bw.write("渠道名称,上级渠道名称,总设备数,在线设备数,离线设备数,未激活设备数,本日变现页UV(今日门户)");
      bw.newLine();

      Statement st = conn.createStatement();
      Statement st1 = conn1.createStatement();
      Statement st2 = conn2.createStatement();
      Statement st3 = conn3.createStatement();

      // 根据渠道统计本日变现页UV(今日门户)
      String sql = "";
      if ("1".equals(level)) {
        sql = "SELECT c.id,i.name,ip.name pname,IFNULL(tc.uv_num,0) AS uv_num FROM channel cp,c_info ip,c_info i,channel c LEFT JOIN"
            + " (SELECT LEFT(UPPER(REPLACE(t.code,'_','#')),5) AS tcode,SUM(uv_num) AS uv_num FROM mail_tp_channeluv t GROUP BY tcode) tc"
            + " ON tc.tcode=c.code WHERE c.pid=cp.id AND c.id=i.cid AND cp.id=ip.cid AND c.weight=1 and c.del_flag='0' ORDER BY uv_num DESC";
      } else if ("2".equals(level)) {
        sql = "SELECT c.id,i.name,ip.name pname,IFNULL(tc.uv_num,0) AS uv_num FROM channel cp,c_info ip,c_info i,channel c LEFT JOIN"
            + " (SELECT LEFT(UPPER(REPLACE(t.code,'_','#')),9) AS tcode,SUM(uv_num) AS uv_num FROM mail_tp_channeluv t GROUP BY tcode) tc"
            + " ON tc.tcode=c.code WHERE c.pid=cp.id AND c.id=i.cid AND cp.id=ip.cid AND c.weight=2  and c.del_flag='0' ORDER BY pname ASC,uv_num DESC";
      }
      ResultSet re = st.executeQuery(sql);
      while (re.next()) {
        String channel_id = (String) re.getString("id");
        String name = (String) re.getString("name");
        String pname = (String) re.getString("pname");
        int uv_num = ((Integer) re.getInt("uv_num")).intValue();
        int total_num = 0;
        int online_num = 0;
        int offline_num = 0;
        int unactive_num = 0;

        // 查询设备总数
        sql = "SELECT COUNT(1) AS c FROM router r,router_distribute rd WHERE r.mac=rd.mac AND rd.cid='" + channel_id + "'";
        ResultSet re1 = st1.executeQuery(sql);
        while (re1.next()) {
          total_num = (int) re1.getInt("c");
        }
        
        /*
        // 查询实时各状态设备数
        sql = "SELECT r.status,COUNT(1) AS c FROM router r,router_distribute rd "
                + " WHERE r.mac=rd.mac AND rd.cid='"+channel_id+"' GROUP BY r.status";
        ResultSet re2 = st2.executeQuery(sql);
        while (re2.next()) {
            int status = (int)re2.getInt("status");
            int r_c = (int)re2.getInt("c");
            if (status == 1) { // 在线
                online_num = r_c;
            } else if (status == 0) { // 离线
                offline_num = r_c;
            } else if (status == -1) { // 未激活
                unactive_num = r_c;
            }
        }
        */

        // 查询未激活设备数
        sql = "SELECT COUNT(1) AS unactive_num FROM router r,router_distribute rd "
            + " WHERE r.mac=rd.mac AND rd.cid='" + channel_id + "' AND r.status=-1";
        ResultSet re2 = st2.executeQuery(sql);
        while (re2.next()) {
          unactive_num = (int) re2.getInt("unactive_num");
        }

        // 查询当天在线设备数(今天在线设备的最大值)
        sql = "SELECT COUNT(DISTINCT mac) online_num FROM( "
            + " SELECT r.mac FROM router r,router_distribute rd "
            + " WHERE r.mac=rd.mac AND rd.cid='" + channel_id + "' AND r.status=1 "
            + " UNION ALL "
            + " SELECT r.mac FROM router r,router_distribute rd "
            + " WHERE r.mac=rd.mac AND rd.cid='" + channel_id + "'"
            + " AND DATE_FORMAT(r.off_time,'%Y-%m-%d') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d')) a";
        ResultSet re3 = st3.executeQuery(sql);
        while (re3.next()) {
          online_num = (int) re3.getInt("online_num");
        }
        // 离线设备数(当天未上线) = 总设备数 - 未激活设备数 - 在线设备数
        offline_num = total_num - unactive_num - online_num;

        String line = name + "," + pname + "," + total_num + "," + online_num + "," + offline_num + "," + unactive_num + "," + uv_num;
        bw.write(line);
        bw.newLine();
      }

      bw.close();
      st2.close();
      st1.close();
      st.close();
      conn2.close();
      conn1.close();
      conn.close();

      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
    }
    return flag;
  }

  /**
   * 将csv文件中路由信息导入到临时表mail_tp_channeluv中
   * 
   * @param uv_list
   * @return
   */
  public static int saveChannelUV(List<Map<String, String>> uv_list) {
    int flag = 0;

    // 先清空临时表
    executeSql("TRUNCATE TABLE mail_tp_channeluv");

    Connection conn = new DBConn().getConn();
    try {
      conn.setAutoCommit(false);
      Statement st = conn.createStatement();
      for (Map<String, String> map : uv_list) {
        // 路由信息入库临时表
        String sql = "REPLACE INTO mail_tp_channeluv(code, uv_num) VALUES('" + map.get("key") + "'," + map.get("value") + ")";
        st.addBatch(sql);
      }
      st.executeBatch();
      conn.commit();
      st.close();
      conn.close();

      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
      try {
        conn.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
    }

    return flag;
  }

  /**
   * 查询当天ES->portal中渠道的本日变现页UV(今日门户)
   */
  public static List<Map<String, String>> queryChannelUVFromES() {
    ElasticSearchClient client = ElasticSearchClient.getInstance();
    String jsonString = "{\"_source\":[\"channel_path\",\"mac\"],\"query\":{\"bool\":{\"must\":[{\"range\":{\"@timestamp\":{\"gte\":\"${{startTime}}\",\"lte\":\"${{endTime}}\"}}}]}},\"aggs\":{\"group_by\":{\"terms\":{\"field\":\"channel_path\",\"size\":\"0\"},\"aggs\":{\"distinct\":{\"cardinality\":{\"field\":\"mac\"}}}}}}";

    /* 由于ElasticSearch时区晚了8小时，查询区间开始时间和结束时间都需要减8小时 */
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Calendar cal = Calendar.getInstance();
    cal.setTime(new Date());
    cal.add(Calendar.DATE, -2);
    String startDate = sdf.format(cal.getTime());
    String startTime = startDate + "T16:00:00";
    cal.add(Calendar.DATE, 1);
    String endDate = sdf.format(cal.getTime());
    String endTime = endDate + "T15:59:59";

    jsonString = jsonString.replace("${{startTime}}", startTime).replace("${{endTime}}", endTime);
    Map response = client.post(es_url, jsonString);
    return resolveGroupDistinctES(response);
  }

  /**
   * 解析ES接口返回的分组并去重数据
   * 
   * @param response
   * @return
   */
  public static List<Map<String, String>> resolveGroupDistinctES(Map response) {
    List<Map<String, String>> list = new ArrayList<Map<String, String>>();
    try {
      List rs = (List) ((Map) ((Map) response.get("aggregations")).get("group_by")).get("buckets");
      for (Object item : rs) {
        try {
          Map jsItem = (Map) item;
          String key = (String) jsItem.get("key");
          Map distinct = (Map) jsItem.get("distinct");
          int value = ((Integer) distinct.get("value")).intValue();
          HashMap<String, String> hm = new HashMap<String, String>();
          hm.put("key", key);
          hm.put("value", String.valueOf(value));
          list.add(hm);
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return list;
  }

  /**
   * 公共执行sql方法
   * 
   * @return
   */
  public static int executeSql(String sql) {
    int flag = 0;
    Connection conn = new DBConn().getConn();
    try {
      Statement st = conn.createStatement();
      st.execute(sql);
      st.close();
      conn.close();

      flag = 1;
    } catch (Exception e) {
      e.printStackTrace();
    }
    return flag;
  }
}
